﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EachRecord.Data
{
    /// <summary>
    /// 数据库操作辅助类
    /// </summary>
  public  class DataHelper
    {
        private static readonly string sqlServerConnectionString = "Data Source=43.139.213.82;Initial Catalog=each;Persist Security Info=True;User ID=sa;Password=Data Source=43.139.213.82;Initial Catalog=each;Persist Security Info=True;User ID=sa;Password=fgb@1357426";

        private static SqlConnection sqlConnection=null;
        public static DataHelper dataHelper { get; set; }

        private DataHelper() {
            try {
                sqlConnection = new SqlConnection(sqlServerConnectionString);
                sqlConnection.Open();
            }
            catch { 
                sqlConnection.Close();
            }
        }

        public static DataHelper GetDataHelper()
        {
            if (dataHelper == null) { 
               dataHelper=new DataHelper();
            }
            return dataHelper;
        }
        /// <summary>
        /// 返回不带参数的添加,修改,删除等操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  int ExecuteCommand(string sql) {
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            return sqlCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// 返回带参数的操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public  int ExecuteCommand(string sql, params SqlParameter[] values) {
            using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection)) {
                sqlCommand.Parameters.AddRange(values);
                return sqlCommand.ExecuteNonQuery();
            }  
        }

        /// <summary>
        /// 返回不带参数的数据库读取操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  SqlDataReader GetReader(string sql) {
            using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection)) {
                SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
                return sqlDataReader;
            } 
        }

        /// <summary>
        /// 返回带参数的数据库读取操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public  SqlDataReader GetReader(string sql,params SqlParameter[] values) {
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            sqlCommand.Parameters.AddRange(values);
            return sqlCommand.ExecuteReader();
        }

        /// <summary>
        /// 返回不带参数的数据表读取操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  DataTable GetDataTable(string sql) {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            return dataSet.Tables[0];
        }

        /// <summary>
        /// 返回带参数的数据表读取操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public  DataTable GetDataTable(string sql, params SqlParameter[] values) {
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            sqlCommand.Parameters.AddRange(values);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
            DataSet dataSet = new DataSet();
            sqlDataAdapter.Fill(dataSet);
            return dataSet.Tables[0];
        }
    }
}
